/** 
 * @Title: PinfoDao.java 
 * @Package com.zizo.autohome.sta.pinfo.dao 
 * @Description: TODO
 * @author HeYan
 * @date 2016年10月26日 下午4:15:15 
 */
package com.zizo.autohome.sta.pinfo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import com.zizo.autohome.carinfo.dao.UpdateBasicParam;
import com.zizo.autohome.sta.pinfo.bean.Pinfo;
import com.zizo.util.ConnectionPool;
import com.zizo.util.Util;

/**
 * @ClassName: PinfoDao
 * @Description: TODO
 * @author HeYan
 * @date 2016年10月26日 下午4:15:15
 * 
 */
public class PinfoDao implements Runnable {
	private Connection conn;
	private int cid;

	public void getInfo1() {
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT bp_mid,bp_engine,bp_cname FROM autohome_other.other_core_basicparam WHERE bp_cid=" + cid
				+ " AND bp_maxyear=(SELECT MAX(bp_maxyear) FROM other_core_basicparam WHERE bp_cid=" + cid
				+ " GROUP BY bp_cid)";
		StringBuffer engin = new StringBuffer();
		StringBuffer gearbox = new StringBuffer();
		StringBuffer seat = new StringBuffer();
		Set<String> set1 = new HashSet<>();
		Set<String> set2 = new HashSet<>();
		Set<String> set3 = new HashSet<>();
		String pinfo_length = "";
		String pinfo_width = "";
		String pinfo_height = "";
		String pinfo_wheelbase = "";
		String pinfo_engin = "";
		String pinfo_gearbox = "";
		String pinfo_seatcnt = "";
		String pinfo_price = "";
		String cname = "";
		float lcj = 0;
		int volume = 0;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int mid = rs.getInt(1);
				String engine_ = rs.getString(2).split(" ")[0];
				set1.add(engine_);
				String[] ss = getCarbody(mid);
				pinfo_wheelbase = ss[3];
				pinfo_length = ss[0];
				pinfo_width = ss[1];
				pinfo_height = ss[2];
				if (!ss[4].equals("-")) {
					set3.add(ss[4]);
				}
				cname = rs.getString(3);
				String[] boxs = getGearBox(mid).split("%%");
				if (boxs.length == 2) {
					if (!boxs[1].equals("CVT")) {
						set2.add(boxs[0] + boxs[1]);
					} else {
						set2.add(boxs[1]);
					}
				}
			}
			for (String s : set1) {
				engin.append(s + "/");
			}
			for (String s : set2) {
				gearbox.append(s + "/");
			}
			for (String s : set3) {
				seat.append(s + "/");
			}
			pinfo_engin = engin.length() > 1 ? engin.substring(0, engin.length() - 1) : "暂无";
			pinfo_gearbox = gearbox.length() > 1 ? gearbox.substring(0, gearbox.length() - 1) : "暂无";
			if (seat.length() < 1) {
				pinfo_seatcnt = "暂无";
			} else {
				pinfo_seatcnt = seat.substring(0, seat.length() - 1);
			}
			pinfo_price = getPrice();
			Pinfo info = new Pinfo(pinfo_length, pinfo_width, pinfo_height, pinfo_wheelbase, pinfo_engin, pinfo_gearbox,
					pinfo_seatcnt, pinfo_price);
			lcj = getLcj();
			volume = getAvgSales();
			insert(info, lcj, volume, cname);
		} catch (SQLException e) {
			System.out.println(cid);
			e.printStackTrace();
		}
	}

	public String[] getCarbody(int mid) {
		Statement st = null;
		ResultSet rs = null;
		String[] res = new String[5];
		String sql = "SELECT t1.cb_length,t1.cb_width,t1.cb_height,t1.cb_wheelbase,t1.cb_seatcount FROM autohome_other.other_core_carbody AS t1 WHERE cb_mid="
				+ mid;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				res[0] = rs.getString(1);
				res[1] = rs.getString(2);
				res[2] = rs.getString(3);
				res[3] = rs.getString(4);
				res[4] = rs.getString(5);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}

	public String getGearBox(int mid) {
		String res = " %% ";
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT t1.bsx_dwgs,t1.bsx_bsxlx FROM autohome_other.other_core_gearbox AS t1 WHERE t1.bsx_mid="
				+ mid;

		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				String s1 = rs.getString(1);
				String s2 = Util.getMatched(rs.getString(2), "[^A-Za-z]").replaceAll("");
				res = s1 + "%%" + s2;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}

	public String getPrice() {
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT MIN(price_zdj),MAX(price_zdj) FROM autohome_other.other_price WHERE price_cid=" + cid;
		String res = "暂无报价";
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				float min = rs.getFloat(1);
				float max = rs.getFloat(2);
				res = min + "~" + max;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}

	public float getLcj() {
		Statement st = null;
		ResultSet rs = null;
		float res = 0;
		String sql = "SELECT ROUND(AVG(price_lcj),2) FROM autohome_other.other_price WHERE price_istrue=1 AND price_cid="
				+ cid + " GROUP BY price_cid";
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				res = rs.getFloat(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}

	public int getAvgSales() {
		Statement st = null;
		ResultSet rs = null;
		int res = 0;
		String sql = "SELECT ROUND(AVG(sales_cnt),0) FROM carvoice_biz.biz_sales WHERE sales_pid=" + cid
				+ " AND YEAR(sales_time)=YEAR(CURRENT_DATE()) GROUP BY sales_pid,YEAR(sales_time)";
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				res = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}

	public void insert(Pinfo info, float lcj, int volume, String cname) {
		PreparedStatement ps = null;
		String sql = "REPLACE INTO carvoice_biz.biz_pinfo(pinfo_cid,pinfo_length,pinfo_width,pinfo_height,pinfo_wheelbase,pinfo_engin,pinfo_gearbox,pinfo_seatcnt,pinfo_price,pinfo_lcj,pinfo_volume,pinfo_cname,pinfo_updatetime) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,now())";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, cid);
			ps.setString(2, info.getPinfo_length());
			ps.setString(3, info.getPinfo_width());
			ps.setString(4, info.getPinfo_height());
			ps.setString(5, info.getPinfo_wheelbase());
			ps.setString(6, info.getPinfo_engin());
			ps.setString(7, info.getPinfo_gearbox());
			ps.setString(8, info.getPinfo_seatcnt());
			ps.setString(9, info.getPinfo_price());
			ps.setFloat(10, lcj);
			ps.setInt(11, volume);
			ps.setString(12, cname);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void run() {
		getInfo1();
		updateBizID();
		System.out.println(cid);
	}

	public PinfoDao(Connection conn, int cid) {
		super();
		this.conn = conn;
		this.cid = cid;
	}

	public void updateBizID() {
		Statement st = null;
		String sql = "UPDATE carvoice_biz.biz_pinfo SET pinfo_bid=(SELECT cid FROM socar_core.relate_car WHERE cid=pinfo_cid);";
		try {
			st = conn.createStatement();
			st.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void main(String[] args) {
		long start = System.currentTimeMillis();
		UpdateBasicParam up = new UpdateBasicParam();
		up.update();
		up.updatecarname();
		ExecutorService fix = Executors.newFixedThreadPool(20);
		Connection conn = ConnectionPool.getConnection("autohome_other", "localhost");
		List<Integer> list = PinfoUtil.getIDs();
		for (int id : list) {
			fix.execute(new PinfoDao(conn, id));
		}
		fix.shutdown();
		while (true) {
			if (fix.isTerminated()) {
				System.out.println((System.currentTimeMillis() - start) / (1000 * 60) + "Min");
				break;
			} else {
				try {
					Thread.sleep(1000 * 600);
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
			}
		}
	}
}
